CREATE VIEW [dbo].[vSoaGroupMemberSummary]
AS
    SELECT  [GroupMemberId],
            [GroupId],
            [PartyId],
            [PartyName],
            CASE WHEN ( [PAID_THRU] IS NULL
                        OR [PAID_THRU] > DATEADD(DAY, 1, GETDATE())
                      )
                      AND ( [BEGIN_DATE] IS NULL
                            OR [BEGIN_DATE] < GETDATE()
                          ) THEN CAST(1 AS BIT)
                 ELSE CAST(0 AS BIT)
            END AS [IsActive],
            [JoinDate],
            [DropDate]
      FROM  [dbo].[vSoaGroupMemberSummarySubscription]
    UNION ALL
    SELECT DISTINCT
            [GroupMemberId],
            [GroupId],
            [PartyId],
            CAST(NULL AS VARCHAR(70)) AS [PartyName],
            CASE WHEN EXISTS ( SELECT   1
                                 FROM   [dbo].[Activity] AS [a]
                                WHERE   [a].[ACTIVITY_TYPE] = 'COMMITTEE'
                                        AND [a].[ID] = [PartyId]
                                        AND [a].[PRODUCT_CODE] = [GroupId]
                                        AND ( ( [a].[EFFECTIVE_DATE] IS NULL
                                                AND ( [a].[THRU_DATE] IS NULL
                                                      OR [a].[THRU_DATE] > GETDATE()
                                                    )
                                              )
                                              OR ( [a].[EFFECTIVE_DATE] <= GETDATE()
                                                   AND ( [a].[THRU_DATE] IS NULL
                                                         OR [a].[THRU_DATE] > GETDATE()
                                                       )
                                                 )
                                            ) ) THEN CAST(1 AS BIT)
                 ELSE CAST(0 AS BIT)
            END AS [IsActive],
            NULL AS [JoinDate],
            NULL AS [DropDate]
      FROM  [dbo].[vSoaGroupMemberSummaryCommittee]
    UNION ALL
    SELECT  [GroupMemberId],
            [GroupId],
            [PartyId],
            [PartyName],
            [IsActive],
            [JoinDate],
            [DropDate]
      FROM  [dbo].[vSoaGroupMemberSummaryEvent]
    UNION ALL
    SELECT  [GroupMemberId],
            [GroupId],
            [PartyId],
            [PartyName],
            [IsActive],
            [JoinDate],
            [DropDate]
      FROM  [dbo].[vSoaGroupMemberSummaryMT]
    UNION ALL
    SELECT  DISTINCT
            [GroupMemberId],
            [GroupId],
            [PartyId],
            [PartyName],
            CASE WHEN EXISTS ( SELECT   1
                                 FROM   [dbo].[Relationship] AS [r]
                                WHERE   ( [r].[ID] = [PartyId] )
                                        AND ( [r].[STATUS] = NULL
                                              OR RTRIM([r].[STATUS]) = ''
                                              OR [r].[STATUS] = 'A'
                                            )
                                        AND ( ( [r].[EFFECTIVE_DATE] IS NULL
                                                AND ( [r].[THRU_DATE] IS NULL
                                                      OR [r].[THRU_DATE] > GETDATE()
                                                    )
                                              )
                                              OR ( [r].[EFFECTIVE_DATE] <= GETDATE()
                                                   AND ( [r].[THRU_DATE] IS NULL
                                                         OR [r].[THRU_DATE] > GETDATE()
                                                       )
                                                 )
                                            ) ) THEN CAST(1 AS BIT)
                 ELSE CAST(0 AS BIT)
            END AS [IsActive],
            NULL AS [JoinDate],
            NULL AS [DropDate]
      FROM  [dbo].[vSoaGroupMemberSummaryRelationship]
    UNION ALL
    SELECT DISTINCT
            [GroupMemberId],
            [GroupId],
            [PartyId],
            [PartyName],
            CAST(1 AS BIT) AS [IsActive],
            NULL AS [JoinDate],
            NULL AS [DropDate]
      FROM  [dbo].[vSoaGroupMemberSummaryOrganization] AS gmso
            INNER JOIN Name AS n ON [n].[ID] = [gmso].[CO_ID]
     WHERE  [n].[COMPANY_RECORD] = 1


GO